import 字段包.UserBean;
import 工具类.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    //查询单个用户的信息
    public UserBean findByUserCode(String userCode, String userPassword) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from smbms_user where userCode =? and userPassword = ? ";
        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, userCode);
            preparedStatement.setString(2, userPassword);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                UserBean user = new UserBean();
                user.setId(resultSet.getInt("id"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setuserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setAddress(resultSet.getString("address"));
                user.setPhone(resultSet.getString("phone"));
                user.setUserRole(resultSet.getInt("userRole"));
                return user;
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    //添加用户的信息
public Integer addUser(UserBean userBean) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql2 = "insert into smbms_user(userName,userCode,userPassword,gender,birthday," +
                "address,phone,userRole,createdBy,creationDate" +
                "values(?,?,?,?,?,?,?,?,?,?))";
        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setString(1, userBean.getUserName());
            preparedStatement.setString(2, userBean.getUserCode());
            preparedStatement.setString(3, userBean.getUserPassword());
            preparedStatement.setInt(4, userBean.getGender());
            preparedStatement.setDate(5, new Date(userBean.getBirthday().getTime()));
            preparedStatement.setString(6, userBean.getAddress());
            preparedStatement.setString(7, userBean.getPhone());
            preparedStatement.setInt(8, userBean.getUserRole());
            preparedStatement.setInt(9, userBean.getCreatedBy());
            preparedStatement.setDate(10, new Date(userBean.getCreationDate().getTime()));
            Integer i = preparedStatement.executeUpdate();
            return i;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //查询所有用户信息
 public List<UserBean> findAll() {
     Connection connection = null;
     PreparedStatement preparedStatement = null;
     ResultSet resultSet = null;
     String sql3 = "select * from smbms_user";
     try {
         connection = JdbcUtils.getConnection();
         preparedStatement = connection.prepareStatement(sql3);
         resultSet = preparedStatement.executeQuery();
         List<UserBean> list = new ArrayList<>();
         while (resultSet.next()) {
             UserBean del = new UserBean();
             del.setId(resultSet.getInt("id"));
             del.setUserName(resultSet.getString("userName"));
             del.setUserCode(resultSet.getString("userCode"));
             del.setuserPassword(resultSet.getString("userPassword"));
             del.setGender(resultSet.getInt("gender"));
             del.setBirthday(resultSet.getDate("birthday"));
             del.setAddress(resultSet.getString("address"));
             del.setPhone(resultSet.getString("phone"));
             del.setUserRole(resultSet.getInt("userRole"));
             list.add(del);
         }
         return list;

     } catch (ClassNotFoundException e) {
         throw new RuntimeException(e);
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }
    }
//删除方法(依据id)
public Integer deleteUserById(Integer id){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
        String sql4 = "delete from smbms_user where id = ?";
    try {
        connection = JdbcUtils.getConnection();
        preparedStatement =connection.prepareStatement(sql4);
        preparedStatement.setInt(1,id);
        int result = preparedStatement.executeUpdate();
        return result;
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }}
//通过id查询信息
public UserBean findById(Integer id){
        Connection  connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql5  ="select * from smbms_user where id = ?";
    try {
        connection = JdbcUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql5);
        preparedStatement.setInt(1,id);
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            UserBean rem = new UserBean();
            rem.setId(resultSet.getInt("id"));
            rem.setUserName(resultSet.getString("userName"));
            rem.setUserCode(resultSet.getString("userCode"));
            rem.setuserPassword(resultSet.getString("userPassword"));
            rem.setGender(resultSet.getInt("gender"));
            rem.setBirthday(resultSet.getDate("birthday"));
            rem.setAddress(resultSet.getString("address"));
            rem.setPhone(resultSet.getString("phone"));
            rem.setUserRole(resultSet.getInt("userRole"));
            return rem;
        }return  null;
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
    }
//修改信息
    public  Integer updateUser(UserBean user){
        Connection  connection = null;
        PreparedStatement preparedStatement = null;
        String sql6 = "update smbms_user set userName =?,userCode=?,userPassword=?," +
                "gender=?,birthday=?,address=?,phone=?,userRole=? where id =?";
        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql6);
            preparedStatement.setString(1, user.getUserName());
            preparedStatement.setString(2, user.getUserCode());
            preparedStatement.setString(3, user.getUserPassword());
            preparedStatement.setInt(4, user.getGender());
            preparedStatement.setDate(5, new Date(user.getBirthday().getTime()));
            preparedStatement.setString(6, user.getAddress());
            preparedStatement.setString(7, user.getPhone());
            preparedStatement.setInt(8, user.getUserRole());
            preparedStatement.setInt(9, user.getId());
            return preparedStatement.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}